﻿#define compiler
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;
using GenerateDataBaseLibrary;
using System.Diagnostics;
using System.IO;

namespace GenerateDataBaseLibrary
{
    public class DataSetToDataBaseMapper : DataSetToDataBaseMapperBase
    {
        /// <summary>
        /// This will hold the query that helps map the data set 
        /// structure into the data base
        /// </summary>
        private StringBuilder requestBuilder;
        /// <summary>
        /// This sql connection is used to connect to the sql server 
        /// instance
        /// </summary>
        private SqlConnection connection;
        /// <summary>
        /// This sql command that helps execute commands creating new data base
        /// as well generating the data base tables on sql server instance from within the
        /// data set structure
        /// </summary>
        private SqlCommand command;

        /// <summary>
        /// Those are both the server name and the database name 
        /// </summary>
        public override string DataBaseName { get; protected set; }
        public override string ServerName { get; protected set; }

        
        public DataSetToDataBaseMapper()
        {
           
        }

        /// <summary>
        /// This is an overloaded constructor
        /// </summary>
        /// <param name="servername">The name of the sql server instance</param>
        /// <param name="databasename">The name of the new created data base</param>
        /// <param name="credentials"></param>
        public DataSetToDataBaseMapper(string servername, string databasename, SqlCredentials credentials = null)
        {
            requestBuilder = new StringBuilder();
            this.DataBaseName = databasename;
            this.ServerName = servername;
            this.Credentials = credentials;
            connection = base.GetConnection(credentials);
            string creationquery = string.Format("USE [master]  IF OBJECT_ID(N'[{0}]',N'U') IS NULL CREATE DATABASE [{0}] ",
                DataBaseName);
            Debug.Print(creationquery);
            try
            {
                connection.Open();
                command = new SqlCommand(creationquery, connection);
                int flag = command.ExecuteNonQuery();
            }
            catch (SqlException caught)
            {
                string message = caught.Message;
            }
            finally
            {
                connection.Close();
                requestBuilder.AppendLine(string.Format("USE [{0}] \n", this.DataBaseName));
            }

        }

        public override bool SaveTextQuery(DataSet dataset)
        {
            bool result;
            string query = BuildRequestFromDataSet(dataset);
            result = true;
            return result;
        }
        /// <summary>
        /// This method will generate the data base structure from the data set
        /// </summary>
        /// <param name="dataset">The data set that will be mapped</param>
        /// <returns>boolean that indicates if the operation is achieved</returns>
        public override bool GenerateFrom(DataSet dataset)
        {
            bool result = false;
            string query = BuildRequestFromDataSet(dataset);

            connection = base.GetConnection(this.Credentials);
            Debug.Print(query + "\n\n\n\n");    
            try
            {
                SqlCommand command = new SqlCommand(query, connection);
                connection.Open();
                int flag = command.ExecuteNonQuery();
                Debug.Print("Data structure has been successfully mapped");
                result = true;
            }
            catch (SqlException caught)
            {
                Console.WriteLine(caught.Message);
                result = false;
            }
            finally
            { connection.Close(); }
            return result;
        }

        /// <summary>
        /// This is a protected method that helps generate the data tables as well as 
        /// the referencial integrity and the relations between the given tables
        /// </summary>
        /// <param name="dataset">The data set that will be mapped</param>
        /// <returns>The query that will generate the data base</returns>
        protected string BuildRequestFromDataSet(DataSet dataset)
        {
            CreateTables(dataset);
            CreateRelations(dataset);
            Debug.Print(requestBuilder.ToString());
            return requestBuilder.ToString();
        }

        /// <summary>
        /// This method will create the query part that generate the data tables
        /// </summary>
        /// <param name="dataset">The data set that will be mapped</param>
        private void CreateTables(DataSet dataset)
        {

            //requestBuilder.Append(string.Format("IF OBJECT_ID (N'[{0}]', N'U') IS NULL\nCREATE DATABASE [{0}]\n", this.DataBaseName));
            requestBuilder.AppendLine(string.Format("USE [{0}]\n", this.DataBaseName));

            foreach (DataTable dt in dataset.Tables)
            {

                requestBuilder.Append(string.Format("IF OBJECT_ID (N'[{0}]', N'U') IS NOT NULL\n" +
                                      "DROP TABLE [{0}]\n" +
                                      "--Create the table [{0}]\n" +
                                      "CREATE TABLE [{0}]\n" +
                 "(\n",dt.TableName));
                    
                           int flag = 0;
                
                         foreach (DataColumn dc in dt.Columns)
                         {
                    if (flag<dt.Columns.Count)
                    {
                        requestBuilder.AppendLine(string.Format("\t[{0}] {1} ,\n", 
                            dc.ColumnName, 
                            DataTypeMappertHelper(dc.DataType.Name))); 
                    }
                    else
                    {
                        requestBuilder.AppendLine(string.Format("\t[{0}] {1} \n",
                            dc.ColumnName,
                            DataTypeMappertHelper(dc.DataType.Name)));
                    }
                }
                requestBuilder.AppendLine(string.Format("\tPRIMARY KEY([{0}]) ", dt.Columns[0].ColumnName));
                requestBuilder.AppendLine(")\n");
            }
        }

        /// <summary>
        /// This method will create the query part that generate the data relations
        /// </summary>
        /// <param name="dataset">The data set that will be mapped</param>
        private void CreateRelations(DataSet dataset)
        {
            foreach (DataRelation datarelation in dataset.Relations)
            {
                requestBuilder.AppendLine(string.Format("ALTER TABLE [{0}] ADD CONSTRAINT ",datarelation.ChildTable.TableName));
                requestBuilder.AppendLine(string.Format(" FK_{0} ",datarelation.ChildKeyConstraint.Columns[0].ColumnName));
                requestBuilder.AppendLine(string.Format(" FOREIGN KEY ([{0}]) ", datarelation.ChildKeyConstraint.Columns[0].ColumnName));       
                requestBuilder.AppendLine(string.Format(" REFERENCES [{0}] ([{1}]) ",datarelation.ParentTable.TableName,
                datarelation.ParentKeyConstraint.Columns[0].ToString()));

            }
        }

        /// <summary>
        /// This helper will map the clr data types to the sql data types
        /// </summary>
        /// <param name="clrdatatype">The clr data type as input</param>
        /// <returns>The sql data type as output</returns>
        protected string DataTypeMappertHelper(string clrdatatype)
        {
            string returnedValue = string.Empty;
            switch (clrdatatype)
            {
                case "Binary":
                    returnedValue = "binary";
                    break;
                case "Boolean":
                    returnedValue = "bit";
                    break;
                case "Byte":
                    returnedValue = "tinyint";
                    break;
                case "DateTime":
                    returnedValue = "datetime";
                    break;
                case "Decimal":
                    returnedValue = "decimal";
                    break;
                case "Double":
                    returnedValue = "double";
                    break;
                case "Object":
                    returnedValue = "varbinary";
                    break;
                case "Guid":
                    returnedValue = "uniqueidentifier";
                    break;
                case "Int16":
                    returnedValue = "smallint";
                    break;
                case "Int32":
                    returnedValue = "int";
                    break;
                case "Int64":
                    returnedValue = "bigint";
                    break;
                case "Money":
                    returnedValue = "money";
                    break;
                case "Single":
                    returnedValue = "real";
                    break;
                case "String":
                    returnedValue = "nvarchar";
                    break;
                case "Xml":
                    returnedValue = "xml";
                    break;
                default:
                    return returnedValue;
            }
            return returnedValue;
        }
    }//Class
}//Namespace
